CREATE TABLE [dbo].[AW_Staff] (
    [StaffId]        UNIQUEIDENTIFIER NOT NULL,
    [Initial]        NVARCHAR (50)    NOT NULL,
    [UserName]       NVARCHAR (50)    NOT NULL,
    [Password]       NVARCHAR (MAX)   NOT NULL,
    [StaffType]      TINYINT          NOT NULL,
    [Name]           NVARCHAR (500)   NOT NULL,
    [SR]             DECIMAL (18, 2)  NOT NULL,
    [SC]             DECIMAL (18, 2)  NOT NULL,
    [SL]             DECIMAL (18, 2)  NOT NULL,
    [SI]             DECIMAL (18, 2)  NOT NULL,
    [RR]             DECIMAL (18, 2)  NOT NULL,
    [RC]             DECIMAL (18, 2)  NOT NULL,
    [RL]             DECIMAL (18, 2)  NOT NULL,
    [RI]             DECIMAL (18, 2)  NOT NULL,
    [Avatar]         NVARCHAR (250)   NULL,
    [Phone]          NVARCHAR (250)   NULL,
    [Mobile]         NVARCHAR (12)    NOT NULL,
    [Fax]            NVARCHAR (250)   NOT NULL,
    [Email]          NVARCHAR (250)   NOT NULL,
    [ListingPercent] DECIMAL (18, 2)  NULL,
    [CreateDate]     DATETIME         NOT NULL,
    [StatusDelete]   TINYINT          NOT NULL,
    [pictureURL]     NVARCHAR (250)   NULL,
    [PhotoId]        VARCHAR (50)     NULL,
    [MediumUrl]      VARCHAR (500)    NULL,
    [SmallUrl]       VARCHAR (500)    NULL,
    [LargeUrl]       VARCHAR (500)    NULL,
    CONSTRAINT [PK_AW_Staff] PRIMARY KEY CLUSTERED ([StaffId] ASC)
);




GO
CREATE TRIGGER [dbo].[AW_Staff_dss_update_trigger] ON [dbo].[AW_Staff] FOR UPDATE AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 308404368 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_Staff_dss_tracking] AS [target] 
USING (SELECT [i].[StaffId] FROM INSERTED AS [i]) AS source([StaffId]) 
ON ([target].[StaffId] = [source].[StaffId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 0, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[StaffId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[StaffId],@marker_create_scope_local_id, @marker_scope_create_peer_key, @marker_scope_create_peer_timestamp, 0, @marker_local_create_peer_timestamp , NULL, 0, 0, GETDATE() );
GO
CREATE TRIGGER [dbo].[AW_Staff_dss_insert_trigger] ON [dbo].[AW_Staff] FOR INSERT AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 308404368 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_Staff_dss_tracking] AS [target] 
USING (SELECT [i].[StaffId] FROM INSERTED AS [i]) AS source([StaffId]) 
ON ([target].[StaffId] = [source].[StaffId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 0, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[StaffId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[StaffId],NULL, NULL, NULL, 0, CAST(@@DBTS AS BIGINT) + 1, NULL, 0, 0, GETDATE() );
GO
CREATE TRIGGER [dbo].[AW_Staff_dss_delete_trigger] ON [dbo].[AW_Staff] FOR DELETE AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 308404368 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_Staff_dss_tracking] AS [target] 
USING (SELECT [i].[StaffId] FROM DELETED AS [i]) AS source([StaffId]) 
ON ([target].[StaffId] = [source].[StaffId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 1, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[StaffId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[StaffId],@marker_create_scope_local_id, @marker_scope_create_peer_key, @marker_scope_create_peer_timestamp, 0, @marker_local_create_peer_timestamp , NULL, 0, 1, GETDATE() );